﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace HttpUploader5.demoAccess.db
{
	/// <summary>
	/// 数据库访问操作
	/// 更新记录：
	///		2012-4-10 创建
	/// </summary>
	public class HttpUploaderDB
	{
		string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DbPath;User Id=admin;Password=;";
		
		public OleDbConnection GetCon()
		{
			string db = HttpContext.Current.Server.MapPath("db.mdb");
			return new OleDbConnection(conString.Replace("DbPath",db));
		}
		
		/// <summary>
		/// 根据UID获取文件列表
		/// </summary>
		/// <param name="uid"></param>
		/// <param name="tb"></param>
		public void GetFilesByUid(int uid,ref DataTable tb)
		{
			string sql = "select * from xdb_files where uid=@uid and IsDeleted=0;";
			OleDbConnection con = this.GetCon();
			OleDbDataAdapter ad = new OleDbDataAdapter(sql, con);
			ad.SelectCommand.Parameters.Add("@uid", OleDbType.Integer);
			ad.SelectCommand.Parameters[0].Value = uid;
			con.Open();
			ad.Fill(tb);
			con.Close();
		}

		/// <summary>
		/// 根据文件ID获取文件信息
		/// </summary>
		/// <param name="fid"></param>
		/// <returns></returns>
		public bool GetFileInfByFid(int fid,ref xdb_files inf)
		{
			bool ret = false;
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			string sql = "select top 1 * from xdb_files where fid=@fid";
			cmd.CommandText = sql;
			cmd.Parameters.Add("@fid", OleDbType.Integer, 4);
			cmd.Parameters[0].Value = fid;
			cmd.Connection.Open();
			OleDbDataReader r = cmd.ExecuteReader();
			if (r.Read())
			{
				inf.fid = fid;
				inf.uid = r.GetInt32(1);
				inf.FileNameLocal = r.GetString(2);
				inf.FileNameRemote = r.GetString(3);
				inf.FilePathLocal = r.GetString(4);
				inf.FilePathRemote = r.GetString(5);
				inf.FilePathRelative = r.GetString(6);
				inf.FileMD5 = r.GetString(7);
				inf.FileLength = Convert.ToInt64(r.GetString(8));
				inf.FileSize = r.GetString(9);
				inf.FilePos = Convert.ToInt64(r.GetString(10));
				inf.PostedLength = Convert.ToInt64(r.GetString(11));
				inf.PostedPercent = r.GetString(12);
				inf.PostComplete = r.GetBoolean(13);
				inf.PostedTime = r.GetDateTime(14);
				inf.IsDeleted = r.GetBoolean(15);
				ret = true;
				r.Close();
			}
			cmd.Connection.Close();
			return ret;
		}

		/// <summary>
		/// 根据文件MD5获取文件信息
		/// </summary>
		/// <param name="md5"></param>
		/// <param name="inf"></param>
		/// <returns></returns>
		public bool GetFileInfByMd5(string md5, ref xdb_files inf)
		{
			bool ret = false;
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			string sql = "select top 1 * from xdb_files where FileMD5=@FileMD5";
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5", OleDbType.VarChar, 32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			OleDbDataReader r = cmd.ExecuteReader();
			if (r.Read())
			{
				inf.fid = r.GetInt32(0);
				inf.uid = r.GetInt32(1);
				inf.FileNameLocal = r.GetString(2);
				inf.FileNameRemote = r.GetString(3);
				inf.FilePathLocal = r.GetString(4);
				inf.FilePathRemote = r.GetString(5);
				inf.FilePathRelative = r.GetString(6);
				inf.FileMD5 = r.GetString(7);
				inf.FileLength = Convert.ToInt64(r.GetString(8));
				inf.FileSize = r.GetString(9);
				inf.FilePos = Convert.ToInt64(r.GetString(10));
				inf.PostedLength = Convert.ToInt64(r.GetString(11));
				inf.PostedPercent = r.GetString(12);
				inf.PostComplete = r.GetBoolean(13);
				inf.PostedTime = r.GetDateTime(14);
				inf.IsDeleted = r.GetBoolean(15);
				ret = true;
				r.Close();
			}
			cmd.Connection.Close();
			return ret;
		}

		/// <summary>
		/// 增加一条数据，并返回新增数据的ID
		/// 在ajax_create_fid.aspx中调用
		/// 文件名称，本地路径，远程路径，相对路径都使用原始字符串。
		/// d:\soft\QQ2012.exe
		/// </summary>
		public int Add(ref xdb_files model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into xdb_files(");
			strSql.Append("FileSize,FilePos,PostedLength,PostedPercent,PostComplete,PostedTime,IsDeleted,uid,FileNameLocal,FileNameRemote,FilePathLocal,FilePathRemote,FilePathRelative,FileMD5,FileLength");
			strSql.Append(") values (");
			strSql.Append("@FileSize,@FilePos,@PostedLength,@PostedPercent,@PostComplete,@PostedTime,@IsDeleted,@uid,@FileNameLocal,@FileNameRemote,@FilePathLocal,@FilePathRemote,@FilePathRelative,@FileMD5,@FileLength");
			strSql.Append(") ");
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			cmd.CommandText = strSql.ToString();
			cmd.Parameters.Add("@FileSize", OleDbType.VarChar,10);
			cmd.Parameters.Add("@FilePos", OleDbType.VarChar,19);
			cmd.Parameters.Add("@PostedLength", OleDbType.VarChar, 19);
            cmd.Parameters.Add("@PostedPercent", OleDbType.VarChar,6);
            cmd.Parameters.Add("@PostComplete", OleDbType.Boolean,1);
            cmd.Parameters.Add("@PostedTime", OleDbType.Date);
            cmd.Parameters.Add("@IsDeleted", OleDbType.Boolean,1);
            cmd.Parameters.Add("@uid", OleDbType.Integer,4);
            cmd.Parameters.Add("@FileNameLocal", OleDbType.VarChar,255);
            cmd.Parameters.Add("@FileNameRemote", OleDbType.VarChar,255);
            cmd.Parameters.Add("@FilePathLocal", OleDbType.LongVarChar);
			cmd.Parameters.Add("@FilePathRemote", OleDbType.LongVarChar);
			cmd.Parameters.Add("@FilePathRelative", OleDbType.LongVarChar);
            cmd.Parameters.Add("@FileMD5", OleDbType.VarChar,32);
			cmd.Parameters.Add("@FileLength", OleDbType.VarChar,19);
            
			cmd.Parameters[0].Value = model.FileSize;
			cmd.Parameters[1].Value = model.FilePos.ToString();
			cmd.Parameters[2].Value = model.PostedLength.ToString();
			cmd.Parameters[3].Value = model.PostedPercent;
			cmd.Parameters[4].Value = model.PostComplete;
			cmd.Parameters[5].Value = model.PostedTime;
			cmd.Parameters[6].Value = model.IsDeleted;
			cmd.Parameters[7].Value = model.uid;
			cmd.Parameters[8].Value = model.FileNameLocal;
			cmd.Parameters[9].Value = model.FileNameRemote;
			cmd.Parameters[10].Value = model.FilePathLocal;
			cmd.Parameters[11].Value = model.FilePathRemote;
			cmd.Parameters[12].Value = model.FilePathRelative;
			cmd.Parameters[13].Value = model.FileMD5;
			cmd.Parameters[14].Value = model.FileLength.ToString();
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			cmd.CommandText = "select top 1 fid from xdb_files order by fid desc;";
			object fid = cmd.ExecuteScalar();
			cmd.Connection.Close();
			return Convert.ToInt32(fid);
		}

		/// <summary>
		/// 更新上传进度
		/// </summary>
		///<param name="uid">用户ID</param>
		///<param name="fid">文件ID</param>
		///<param name="filePos">文件位置，大小可能超过2G，所以需要使用long保存</param>
		///<param name="postedLength">已上传长度，文件大小可能超过2G，所以需要使用long保存</param>
		///<param name="postedPercent">已上传百分比</param>
		public bool UpdateProgress(int uid,int fid,long filePos,long postedLength,string postedPercent)
		{
			string sql = "update xdb_files set FilePos=@FilePos,PostedLength=@PostedLength,PostedPercent=@PostedPercent where uid=@uid and fid=@fid";
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FilePos", OleDbType.VarChar,19);
			cmd.Parameters.Add("@PostedLength", OleDbType.VarChar,19);
			cmd.Parameters.Add("@PostedPercent", OleDbType.VarChar, 6);
			cmd.Parameters.Add("@uid", OleDbType.Integer);
			cmd.Parameters.Add("@fid", OleDbType.Integer);
			cmd.Parameters[0].Value = filePos.ToString();
			cmd.Parameters[1].Value = postedLength.ToString();
			cmd.Parameters[2].Value = postedPercent;
			cmd.Parameters[3].Value = uid;
			cmd.Parameters[4].Value = fid;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
			return true;
		}

		/// <summary>
		/// 上传完成。将所有相同MD5文件进度都设为100%
		/// </summary>
		public void UploadComplete(string md5)
		{
			string sql = "update xdb_files set PostedLength=FileLength,PostedPercent='100%',PostComplete=1 where FileMD5=@FileMD5";
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5",OleDbType.VarChar,32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
		}

		/// <summary>
		/// 检查相同MD5文件是否有已经上传完的文件
		/// </summary>
		/// <param name="md5"></param>
		public bool HasCompleteFile(string md5)
		{
			//为空
			if (string.IsNullOrEmpty(md5)) return false;

			string sql = "select fid from xdb_files where PostComplete=1 and FileMD5=@FileMD5";
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5", OleDbType.VarChar, 32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			object ret = cmd.ExecuteScalar();
			cmd.Connection.Close();
			cmd.Dispose();

			return ret != null;
		}

		/// <summary>
		/// 删除一条数据，并不真正删除，只更新删除标识。
		/// </summary>
		/// <param name="uid"></param>
		/// <param name="fid"></param>
		public void Delete(int uid,int fid)
		{
			string sql = "update xdb_files set IsDeleted=1 where uid=@uid and fid=@fid";
			OleDbConnection con = this.GetCon();
			OleDbCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@uid", OleDbType.Integer);
			cmd.Parameters.Add("@fid", OleDbType.Integer);
			cmd.Parameters[0].Value = uid;
			cmd.Parameters[1].Value = fid;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
		}
	}
}
